package shiyanwu;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class shiyan5 {
	public static class shiyanwu {

		private static Scanner sc = new Scanner(System.in);

		public static Connection getConnection() {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				// 加载MYSQL的JDBC驱动程序
				System.out.println("==================JDBC驱动程序加载成功===================");
				// DriverManager.getConnection("jdbc:mysql://localhost:3306/sct",
				// "root", "402022");
				System.out.println("============程序已开始尝试连接数据库===============");
				return DriverManager.getConnection("jdbc:mysql://localhost:3306/wx", "root", "123456");
			} catch (Exception e) {
				System.out.print("====================JDBC驱动程序加载失败===================");
				e.printStackTrace();
			}
			return null;
		}

		public static String sayMenu() {
			System.out.println("===============请输入相应的命令菜单：==============");
			System.out.println(" 1 查询数据；");
			System.out.println(" 2 添加数据；");
			System.out.println(" 3 修改数据；");
			System.out.println(" 4 删除数据；");
			System.out.print("请输入命令编号：");
		
			return sc.nextLine();
		}

		public static void main(String[] args) {

			try {
				// 获取数据库的链接
				Connection connect = getConnection();
				String Sno,Sname,sex,dept;
				int age;
				if (connect == null) {
					System.out.println("============程序连接数据库失败===============");
				} else {
					System.out.println("============程序已成功连接数据库===============");
				}
				Statement stmt = connect.createStatement();
				Scanner in=new Scanner(System.in);
				while (true) {
					String str = sayMenu();
					if ("out".equals(str)) {
						System.out.println("=================欢迎您的下次使用==============");
						sc.close();
						break;
					} else {
						switch (str) {
						case "1":
							// 1 查询数据；
							System.out.println("请输入您想查询的学号");
							Sno=in.nextLine();
									ResultSet rs = stmt.executeQuery("select * from student where Sno='"+Sno+"'");
							System.out.println("===============程序开始查询结果为：========================");
							System.out.println("+------+-------------+------+");
							System.out.println("| Sno | Sname | sex | age | dept");
							System.out.println("+------+-------------+------+");
							while (rs.next()) {
								System.out.println("| " + rs.getString("Sno") + "  | " + rs.getString("Sname") + " | "
										+ rs.getString("sex") + rs.getString("age")+rs.getString("dept"));
							}
							System.out.println("+------+-------------+------+-------+");
							break;
						case "2":
							// 2 添加数据；
							// 增加记录后查询结果
							System.out.println("请输入学号、姓名、性别、年龄、学院：");
							Sno=in.nextLine();
							Sname=in.nextLine();
							sex=in.nextLine();
							age=in.nextInt();
							dept=in.nextLine();
							System.out.println("===============程序增加1条记录后查询结果为：====================");
							stmt.executeUpdate("insert into student values('"+Sno+"','"+Sname+"','"+sex+"','"+age+"','"+dept+"');");
							rs = stmt.executeQuery("select * from student");
							System.out.println("+------+-------------+------+-------+");
							System.out.println("|Sno | Sname | sex | age  | dept  |");
							System.out.println("+------+-------------+------+-------+");
							while (rs.next()) {
								System.out.println("| " + rs.getString("Sname") + "  | " + rs.getString("sex") + " | "
										+ rs.getString("age") + "   | " + rs.getString("dept") + "     |");
							}
							System.out.println("+------+-------------+------+-------+");
							break;
						case "3":
							// 3 修改数据；
							System.out.println("请输入学号、姓名");
							Sno=in.nextLine();
							Sname=in.nextLine();
							System.out.println("================修改信息后查询结果为=================");
							stmt.executeUpdate("update student set Sno='"+Sno+"' where Sname='"+Sname+"';");
							rs = stmt.executeQuery("select * from student");
							System.out.println("+------+-------------+------+-------+");
							System.out.println("| name |     sex     |  age | dept  |");
							System.out.println("+------+-------------+------+-------+");
							while (rs.next()) {
								System.out.println(rs.getString("Sno") + "  | "+"| " + rs.getString("Sname") + "  | " + rs.getString("sex") + " | "
										+ rs.getString("age") + "   | " + rs.getString("dept") + "     |");
							}
							System.out.println("+------+-------------+------+-------+");
							break;
						case "4":
							// 4 删除数据；
		
							System.out.println("请输入姓名：");
							Sname=in.nextLine();
							System.out.println("=============删除学生记录后查询结果为：===============");
							stmt.executeUpdate("delete from student where Sname= '"+Sname+"';");
							rs = stmt.executeQuery("select * from student");
							System.out.println("+------+-------------+------+-------+");
							System.out.println("| name |     sex     | age  | dept  |");
							System.out.println("+------+-------------+------+-------+");
							while (rs.next()) {
								System.out.println("| " + rs.getString("Sname") + "  | " + rs.getString("sex") + " | "
										+ rs.getString("age") + "   | " + rs.getString("dept") + "     |");
							}
							System.out.println("+------+-------------+------+-------+");
							break;
						default:
							System.out.println("==================您输入的参数有误==================");
							break;
						}
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}
